package controllers;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import jj.play.ns.nl.captcha.noise.StraightLineNoiseProducer;
import models.Excel;
import play.db.jpa.Model;
import utils.ExcelReader;

public class DataInsert {

	 /*
	 * public static String driver = "com.mysql.jdbc.Driver"; public static
	 * String url =
	 * "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8"
	 * ;//之所以链接地址后面会添加参数，是为了防止中文乱码 public static Connection conn;
	 */
	static String address = "G:\\小盒计划\\分析软件\\88.xls";
	static String table = "88";
	
	public static void insertData1(String tbName){
		try {
			 Excel excel = new Excel();
			//casilin:插入数据，先从excel中读取数据
			InputStream is = new FileInputStream(address);
			ExcelReader excelReader = new ExcelReader();
			String[] colName = excelReader.readExcelTitle(is);
		    
		    //开始建立插入的sql语句,每一次插入的开头都是不变的,都是字段名
		    StringBuffer sqlBegin = new StringBuffer("insert into " + tbName + "(");
		    //获取字段名，并添加入sql语句中
		   /* excel.time=colName[1];
			excel.name=colName[2];
			excel.type=colName[3];
			excel.number=Integer.parseInt(colName[4]);
			excel.tableName = table;
			excel.save();*/
			colName[1] = "time";
			colName[2] = "name";
			colName[3] = "type";
			colName[4] = "number";
		    for (int i = 0; i < colName.length; i ++){
		    	sqlBegin.append(colName[i]);
		    	if (i != colName.length -1) {
		    		sqlBegin.append(",");
		    	}
		    }
		  
			// casilin:插入数据，先从excel中读取数据
			// 开始建立插入的sql语句,每一次插入的开头都是不变的,都是字段名
			// 获取字段名，并添加入sql语句中
			
			System.out.println("标题存进去了");
		    sqlBegin.append(") values(");
		    is.close();
		    
		    //下面读取字段内容
		    POIFSFileSystem fs;
		    HSSFWorkbook wb;
		    HSSFSheet sheet;
		    HSSFRow row;
		      
		    is = new FileInputStream(address);
	        fs = new POIFSFileSystem(is);    
	        wb = new HSSFWorkbook(fs);  
	        sheet = wb.getSheetAt(0);
	            
	        //得到总行数    
	        int rowNum = sheet.getLastRowNum();    
	        row = sheet.getRow(0);    
	        int colNum = row.getPhysicalNumberOfCells();    
	        //正文内容应该从第二行开始,第一行为表头的标题    
	        String sql = new String(sqlBegin);
	        String temp;
	        for (int i = 0; i <= rowNum; i++) {    
	            row = sheet.getRow(i);    
	            int j = 0;    
	            while (j<colNum) {       
	            	temp = excelReader.getStringCellValue(row.getCell((short) j)).trim();
	            	
	            	//日期的特殊处理
	            	if (colName[j].indexOf("date") != -1){
	            		temp = temp.substring(0, temp.length()-2);
	            		//excel是以1990年为基数的，而java中的date是以1970年为基数的。所以要扣除差 25569天
	            		Date d = new Date((Long.valueOf(temp) - 25569) * 24 * 3600 * 1000);
	            		DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
	            		temp = "'" + formater.format(d) + "'";
	            	}
	            	
	                sql = sql + temp;
	                if (j != colNum-1){
	                	sql = sql + ",";
	                }
	                j ++;    
	            }       
	            sql = sql + ")";
	            System.out.println(sql.toString());
	            sql = "";
	            sql = sqlBegin.toString();
	            Model.em().createNativeQuery(sql).executeUpdate();
	        }
			
		}  catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  catch (IOException e) {    
            e.printStackTrace();    
        } 
	}
	public static void insertData() {
		try {
			Excel excel = new Excel();
			// casilin:插入数据，先从excel中读取数据
			InputStream is = new FileInputStream(address);
			ExcelReader excelReader = new ExcelReader();
			String[] colName = excelReader.readExcelTitle(is);
			// 开始建立插入的sql语句,每一次插入的开头都是不变的,都是字段名
			// 获取字段名，并添加入sql语句中
			/*excel.time=colName[1];
			excel.name=colName[2];
			excel.type=colName[3];
			excel.number=Integer.parseInt(colName[4]);
			excel.tableName = table;*/
			excel.save();
			System.out.println("标题存进去了");
			is.close();
			// 下面读取字段内容
			POIFSFileSystem fs;
			HSSFWorkbook wb;
			HSSFSheet sheet;
			HSSFRow row;
			is = new FileInputStream(address);
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(0);

			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			row = sheet.getRow(0);
			int colNum = row.getPhysicalNumberOfCells();
			// 正文内容应该从第二行开始,第一行为表头的标题
			String temp;
			for (int i = 1; i <= rowNum; i++) {
				String[] temps=new String[4];
				row = sheet.getRow(i);
				int j = 0;
				//System.out.println("row==="+row);
				while (j < colNum) {
					excel = new Excel();
					temp = excelReader.getStringCellValue(row.getCell((short) j)).trim();
					if(!temp.equals(""))
					// 日期的特殊处理
					if (colName[j].indexOf("date") != -1) {
						temp = temp.substring(0, temp.length() - 2);
						// excel是以1990年为基数的，而java中的date是以1970年为基数的。所以要扣除差 25569天
						Date d = new Date((Long.valueOf(temp) - 25569) * 24 * 3600 * 1000);
						DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
						temp = formater.format(d);
					}
					switch (j%colNum) {
					case 1:
						temps[j%colNum-1] = temp;
						
						break;
					case 2:
						temps[j%colNum-1] = temp;
						
						break;
					case 3:
						temps[j%colNum-1] = temp;
						
						break;
					case 4:
						temps[j%colNum-1] = temp;
						excel.time=temps[0];
						excel.name=temps[1];
						excel.type=temps[2];
						//System.out.println(temps[3]);
						excel.number=Integer.parseInt(temps[3]);
						excel.tableName = table;
						excel.save();
						break;	
					}
					j++;
				}
			}
			System.out.println("数据存进去了");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
